CREATE TABLE [dbo].[tb_Concursos]
(
[idConcurso] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Convocatoria] [int] NOT NULL,
[Fecha] [datetime] NULL,
[Coordinacion] [int] NULL,
[Localidad] [int] NULL,
[Nivel] [int] NULL,
[Modalidad] [int] NULL,
[Estado] [int] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatetb_Concursos] ON [dbo].[tb_Concursos]
 FOR UPDATE AS
SET NOCOUNT ON 
 IF (update(idConcurso)) begin 
	 SELECT * FROM inserted 
	 IF @@ROWCOUNT = 1 begin 
		 UPDATE tb_Vacantes
		 SET Concurso = (SELECT idConcurso FROM inserted)
		 WHERE tb_Vacantes.Concurso = (SELECT idConcurso FROM deleted)
	end  ELSE begin 
		 ROLLBACK TRANSACTION 
 end 
 end

GO
ALTER TABLE [dbo].[tb_Concursos] ADD CONSTRAINT [PK_tb_Concursos] PRIMARY KEY CLUSTERED  ([idConcurso]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Concursos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Concursos_tb_Convocatorias] FOREIGN KEY ([Convocatoria]) REFERENCES [dbo].[tb_Convocatorias] ([idConvocatoria])
GO
ALTER TABLE [dbo].[tb_Concursos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Concursos_tb_Organizaciones] FOREIGN KEY ([Coordinacion]) REFERENCES [dbo].[tb_Organizaciones] ([idOrganizaciones])
GO
ALTER TABLE [dbo].[tb_Concursos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Concursos_tb_EstadosConcursos] FOREIGN KEY ([Estado]) REFERENCES [dbo].[tb_EstadosConcursos] ([idEstadoConcurso])
GO
ALTER TABLE [dbo].[tb_Concursos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Concursos_tb_Localidades] FOREIGN KEY ([Localidad]) REFERENCES [dbo].[tb_Localidades] ([idLocalidad])
GO
ALTER TABLE [dbo].[tb_Concursos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Concursos_tb_ModalidadesEnsenanza] FOREIGN KEY ([Modalidad]) REFERENCES [dbo].[tb_ModalidadesEnsenanza] ([idModalidad])
GO
ALTER TABLE [dbo].[tb_Concursos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Concursos_tb_NivelesEnsenanza] FOREIGN KEY ([Nivel]) REFERENCES [dbo].[tb_NivelesEnsenanza] ([idNivelEnsenanza])
GO
ALTER TABLE [dbo].[tb_Concursos] NOCHECK CONSTRAINT [FK_tb_Concursos_tb_Organizaciones]
GO
ALTER TABLE [dbo].[tb_Concursos] NOCHECK CONSTRAINT [FK_tb_Concursos_tb_EstadosConcursos]
GO
ALTER TABLE [dbo].[tb_Concursos] NOCHECK CONSTRAINT [FK_tb_Concursos_tb_ModalidadesEnsenanza]
GO
ALTER TABLE [dbo].[tb_Concursos] NOCHECK CONSTRAINT [FK_tb_Concursos_tb_NivelesEnsenanza]
GO
